package cn.com.nes.common.util.listener;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import cn.com.nes.common.util.DBUtils;
import cn.com.nes.common.util.HttpUtils;

/*
 * 发送预警短信
*/
public class AppTimerLogic implements Runnable {

	private Connection conn;

	public AppTimerLogic(Connection conn, Connection conn_click) {
		if (conn != null)
			this.conn = conn;
	}

	@Override
	public void run() {
		try {
			conn = DBUtils.getJNDIDBConn();
			timerFunCustom();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	public Map<String, String> getAreaCodeByPhone(String phone) throws SQLException {
		String res = "11";
		StringBuffer sb = new StringBuffer();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, String> map = new HashMap<>();
		map.put("xzqhdm", res);
		map.put("xzqhmc", "北京");
		try {
			Map<String, String> adderssMap = HttpUtils.getPhoneAddress(phone);
			String areaHome = adderssMap.get("province") + " " + adderssMap.get("city");
			if (!StringUtils.isBlank(areaHome)) {
				sb.append(
						"SELECT xzqhdm from t_xzqh where xzqhqc like concat(?,'%') and xzqhqc like concat('%',?,'%') order by xzqhjb limit 1 ");
				ps = conn.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
				ps.setString(1, adderssMap.get("province"));
				ps.setString(2, adderssMap.get("city"));
				rs = ps.executeQuery();
				if (rs.next()) {
					res = rs.getString("xzqhdm");
				}
				map.put("xzqhdm", res);
				map.put("xzqhmc", areaHome);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null)
				ps.close();
			if (rs != null)
				rs.close();
		}
		return map;
	}

	public void timerFunCustom() throws SQLException {
		StringBuffer sb = new StringBuffer();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			// 查询线索
			sb.delete(0, sb.length());
			sb.append(" SELECT uuid,cluephone from clue where (xzqhdm='86' or xzqhdm is null or xzqhdm='') and isdelete=0 limit 500 "); //
			ps = conn.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			List<Map<String, String>> dataList = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> map = new HashMap<>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k), rs.getString(k) == null ? "" : rs.getString(k));
				}
				dataList.add(map);
			}
			rs.close();
			ps.close();

			for (int i = 0; i < dataList.size(); i++) {
				String uuid = dataList.get(i).get("uuid");
				String cluephone = dataList.get(i).get("cluephone");
				Map<String, String> item = getAreaCodeByPhone(cluephone);
				// 查询设备
				sb.delete(0, sb.length());
				sb.append("update  clue set xzqhdm = ?,numberhome=? where cluephone = ? and  (xzqhdm='86' or xzqhdm is null or xzqhdm='') and isdelete=0 ");
				ps = conn.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
				ps.setString(1, item.get("xzqhdm"));
				ps.setString(2, item.get("xzqhmc"));
				ps.setString(3, cluephone);
				ps.execute();
				conn.commit();
				ps.close();
				System.out.println(i + "/" + dataList.size());

			}

			// 查询抽奖记录
			// 查询线索
			sb.delete(0, sb.length());
			sb.append(" SELECT userPhone from active_record where (numberhome is null or numberhome='') limit 500 "); //
			ps = conn.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rsmd = rs.getMetaData();
			columnCount = rsmd.getColumnCount();
			dataList = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> map = new HashMap<>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k), rs.getString(k) == null ? "" : rs.getString(k));
				}
				dataList.add(map);
			}
			rs.close();
			ps.close();

			for (int i = 0; i < dataList.size(); i++) {
				String cluephone = dataList.get(i).get("userPhone");
				Map<String, String> item = getAreaCodeByPhone(cluephone);
				// 查询设备
				sb.delete(0, sb.length());
				sb.append(
						"update  active_record set numberhome = ? where userPhone = ? and   (numberhome is null or numberhome='') ");
				ps = conn.prepareStatement(sb.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
				ps.setString(1, item.get("xzqhmc"));
				ps.setString(2, cluephone);
				ps.execute();
				conn.commit();
				ps.close();
				System.out.println(i + "/" + dataList.size());

			}

		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
	}

	public static void main(String[] args) throws SQLException {
		Connection conn2 = DBUtils.getJdbcForDB();
		try {
			AppTimerLogic l = new AppTimerLogic(conn2, null);
			l.timerFunCustom();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn2.close();
		}

	}

}
